/// Merge stripe payments with hugo actions dataset

********************************************************************************
*** Wide-Merge on ID and timestamp

import delimited using "data/inputs/hugo/stripe_payments.csv", clear
rename customer_id stripecustomerid
gen timestamp_stripepayment = clock(created, "MD20Yhm")
format timestamp_stripepayment %tc
rename amount stripe_amount
rename seller_message stripe_message
rename card_funding card_type 

sort stripecustomerid timestamp_stripepayment

by stripecustomerid: gen stripeindex = _n
keep stripecustomerid stripeindex status stripe_amount timestamp_stripepayment stripe_message ///
	card_type
rename timestamp_stripepayment timestamp
reshape wide stripe_amount status stripe_message card_type, ///
	i(stripecustomerid timestamp) j(stripeindex)

save "data/int/widestripetime", replace

use "data/cleaned/hugo/All balance actions.dta", clear

drop if mi(stripecustomerid)
sort stripecustomerid action_index
gen double damount = amount
replace damount = round(damount, .01)

sort stripecustomerid action_index
by stripecustomerid: gen hugoindex = _n

keep id canceled timestamp_bind threemonth damount amount_paid source type subtype stripecustomerid timestamp ///
	hugoindex reservebalanceindays daily bound clean_expt

quietly reshape wide damount amount_paid source type subtype reservebalanceindays, ///
	i(id daily bound canceled timestamp_bind threemonth stripecustomerid timestamp) j(hugoindex)

merge 1:1 stripecustomerid timestamp using "data/int/widestripetime"

quietly reshape long damount amount_paid source type subtype status reservebalanceindays ///
	stripe_message stripe_amount card_type, ///
	i(id daily bound canceled threemonth stripecustomerid timestamp) j(index)

sort stripecustomerid timestamp

drop if mi(damount) & mi(stripe_amount)

*keep only those with at least one hugo transaction
bysort stripecustomerid: egen hashugo = mean(damount)
drop if mi(hashugo)
drop hashugo index

order stripecustomerid daily bound damount reservebalanceindays source type subtype ///
	timestamp status stripe_amount stripe_message card_type _merge
	
bysort stripecustomerid: egen firstpay = min(timestamp)
count if timestamp == firstpay & status=="Refunded"
bysort stripecustomerid: egen threemonthall = max(threemonth)
bysort stripecustomerid: egen bindall = max(timestamp_bind)
gen stripe_amountpaid = stripe_amount if inlist(status, "Paid","Refunded") & ///
	timestamp<threemonthall-(1000*60*60)

gen stripe_refund = stripe_amount if status=="Refunded"
bysort stripecustomerid: egen totdamount_paid = sum(amount_paid)
bysort stripecustomerid: egen totstripe_amountpaid = sum(stripe_amountpaid)
bysort stripecustomerid: egen totstripe_refund = sum(stripe_refund)
bysort stripecustomerid: egen wascanceled = max(canceled)

order stripecustomerid timestamp_bind threemonth timestamp source type subtype amount_paid ///
	status stripe_amountpaid totdamount_paid totstripe_amountpaid

bysort stripecustomerid: egen preref = max(status=="Refunded" & timestamp-bindall<0)

assert abs(totdamount_paid - totstripe_amountpaid)<1 if preref==0
assert preref==1 if abs(totdamount_paid - totstripe_amountpaid)>1
	
bysort stripecustomerid: egen allid = mode(id)
drop id
rename allid id

save "data/cleaned/stripe/hugo stripe timeline.dta", replace
	
beep
